package cn.bdqn.brand.dao;

import cn.bdqn.brand.pojo.Brand;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 品牌数据访问层
 */
public class BrandDao {

    Connection connection;

    PreparedStatement psmt;

    ResultSet rs;

    /**
     * 查询所有的品牌信息
     *
     * @return 所有信息集合
     */
    public List<Brand> getAllBrands() {
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setDatabaseName("itheima");
        dataSource.setUser("root");
        dataSource.setPassword("123456");

        // 获取连接
        try {
            connection = dataSource.getConnection();

            // 查询所有数据
            String sql = "SELECT * FROM tb_brand";

            // 预编译
            psmt = connection.prepareStatement(sql);

            // 执行sql语句
            rs = psmt.executeQuery();

            // 将结果集中的数据取出封装到javabean
            List<Brand> brands = new ArrayList<>();
            while (rs.next()) {
                Brand brand = new Brand();
                brand.setId(rs.getInt("id"));
                brand.setBrandName(rs.getString("brand_name"));
                brand.setCompanyName(rs.getString("company_name"));
                brand.setDescription(rs.getString("description"));
                brand.setOrdered(rs.getInt("ordered"));
                brand.setStatus(rs.getInt("status"));

                brands.add(brand);
            }

            return brands;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (psmt != null) {
                    psmt.close();
                }
                if (connection != null) {
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    /**
     * 删除品牌数据
     *
     * @param id 主键id
     * @return 删除的条数
     */
    public int delBrand(int id) {
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setDatabaseName("itheima");
        dataSource.setUser("root");
        dataSource.setPassword("123456");

        try {
            connection = dataSource.getConnection();

            // 查询所有数据
            String sql = "DELETE FROM tb_brand WHERE id = ?";

            // 预编译
            psmt = connection.prepareStatement(sql);

            psmt.setInt(1, id);

            // 执行
            int rs = psmt.executeUpdate();

            return rs;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (psmt != null) {
                    psmt.close();
                }
                if (connection != null) {
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return 0;
    }

    /**
     * 添加品牌信息
     *
     * @param brand 品牌信息
     * @return 影响行数
     */
    public int addBrand(Brand brand) {
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setDatabaseName("itheima");
        dataSource.setUser("root");
        dataSource.setPassword("123456");

        try {
            connection = dataSource.getConnection();

            // 查询所有数据
            String sql = "INSERT INTO tb_brand VALUES (0,?,?,?,?,?)";

            // 预编译
            psmt = connection.prepareStatement(sql);

            psmt.setString(1, brand.getBrandName());
            psmt.setString(2, brand.getCompanyName());
            psmt.setInt(3, brand.getOrdered());
            psmt.setString(4, brand.getDescription());
            psmt.setInt(5, brand.getStatus());

            // 执行
            int rs = psmt.executeUpdate();

            return rs;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (psmt != null) {
                    psmt.close();
                }
                if (connection != null) {
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return 0;
    }

    /**
     * 查询所有的品牌信息
     *
     * @return 所有信息集合
     */
    public Brand getBrandById(int id) {
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setDatabaseName("itheima");
        dataSource.setUser("root");
        dataSource.setPassword("123456");

        // 获取连接
        try {
            connection = dataSource.getConnection();

            // 查询所有数据
            String sql = "SELECT * FROM tb_brand WHERE id = ?";

            // 预编译
            psmt = connection.prepareStatement(sql);

            psmt.setInt(1, id);

            // 执行sql语句
            rs = psmt.executeQuery();

            // 将结果集中的数据取出封装到javabean
            Brand brand = new Brand();
            if (rs.next()) {
                brand.setId(rs.getInt("id"));
                brand.setBrandName(rs.getString("brand_name"));
                brand.setCompanyName(rs.getString("company_name"));
                brand.setDescription(rs.getString("description"));
                brand.setOrdered(rs.getInt("ordered"));
                brand.setStatus(rs.getInt("status"));
            }

            return brand;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (psmt != null) {
                    psmt.close();
                }
                if (connection != null) {
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    /**
     * 修改品牌信息
     *
     * @param brand 品牌信息
     * @return 影响行数
     */
    public int updateBrand(Brand brand) {
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setDatabaseName("itheima");
        dataSource.setUser("root");
        dataSource.setPassword("123456");

        try {
            connection = dataSource.getConnection();

            // 查询所有数据
            String sql = "UPDATE tb_brand SET brand_name=?, " +
                    "company_name=?, ordered=?,description=?,status=? WHERE id=?";

            // 预编译
            psmt = connection.prepareStatement(sql);

            psmt.setString(1, brand.getBrandName());
            psmt.setString(2, brand.getCompanyName());
            psmt.setInt(3, brand.getOrdered());
            psmt.setString(4, brand.getDescription());
            psmt.setInt(5, brand.getStatus());
            psmt.setInt(6, brand.getId());

            // 执行
            int rs = psmt.executeUpdate();

            return rs;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (psmt != null) {
                    psmt.close();
                }
                if (connection != null) {
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return 0;
    }
}
